﻿namespace AnyOffice.DAL
{
    using AnyOffice.Components;
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class EmailDao
    {
        public bool ChangeBox(SqlConnection conn, int emailId, int boxId, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE ");
            builder.Append("\t[dbo].[Email] ");
            builder.Append("SET ");
            builder.Append("\t[BoxId] = @BoxId ");
            builder.Append("WHERE ");
            builder.Append("\t[EmailId] = @EmailId ");
            builder.Append("\tAND [ToId] = @UserId ");
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@EmailId", SqlDbType.Int);
            commandParameters[0].Value = emailId;
            commandParameters[1] = new SqlParameter("@BoxId", SqlDbType.Int);
            commandParameters[1].Value = boxId;
            commandParameters[2] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[2].Value = userId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public bool Delete(SqlConnection conn, int emailId, DeleteType deleteType, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DECLARE @AttachmentId nvarchar(50) ");
            builder.Append("IF @DeleteType =0 ");
            builder.Append("BEGIN ");
            builder.Append("\tSELECT ");
            builder.Append("\t\t@AttachmentId = [AttachmentId] ");
            builder.Append("\tFROM ");
            builder.Append("\t\t[dbo].[Email] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[EmailId] = @EmailId ");
            builder.Append("\t\tAND [ToId] = @UserId ");
            builder.Append("\t\tAND [DeleteFlag] IN (0,2) ");
            builder.Append("\t\tAND ISNULL([AttachmentId],'') <> '' ");
            builder.Append("\tUPDATE ");
            builder.Append("\t\t[dbo].[Email] ");
            builder.Append("\tSET ");
            builder.Append("\t\t[DeleteFlag] = [DeleteFlag] + 1 ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[EmailId] = @EmailId ");
            builder.Append("\t\tAND [ToId] = @UserId ");
            builder.Append("\t\tAND [DeleteFlag] IN (0,2) ");
            builder.Append("\tDELETE FROM ");
            builder.Append("\t\t[dbo].[Email] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[EmailId] = @EmailId ");
            builder.Append("\t\tAND [ToId] = @UserId ");
            builder.Append("\t\tAND [DeleteFlag] > 2 ");
            builder.Append("END ");
            builder.Append("ELSE ");
            builder.Append("BEGIN ");
            builder.Append("\tSELECT ");
            builder.Append("\t\t@AttachmentId = [AttachmentId] ");
            builder.Append("\tFROM ");
            builder.Append("\t\t[dbo].[Email] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[EmailId] = @EmailId ");
            builder.Append("\t\tAND [FromId] = @UserId ");
            builder.Append("\t\tAND [DeleteFlag] IN (0,1) ");
            builder.Append("\t\tAND ISNULL([AttachmentId],'') <> '' ");
            builder.Append("\tUPDATE ");
            builder.Append("\t\t[dbo].[Email] ");
            builder.Append("\tSET ");
            builder.Append("\t\t[DeleteFlag] = [DeleteFlag] + 2 ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[EmailId] = @EmailId ");
            builder.Append("\t\tAND [FromId] = @UserId\t\t ");
            builder.Append("\t\tAND [DeleteFlag] IN (0,1) ");
            builder.Append("\t ");
            builder.Append("\tDELETE FROM ");
            builder.Append("\t\t[dbo].[Email] ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[EmailId] = @EmailId ");
            builder.Append("\t\tAND [FromId] = @UserId  ");
            builder.Append("\t\tAND [DeleteFlag] > 2 ");
            builder.Append("END ");
            builder.Append("IF @AttachmentId IS NOT NULL ");
            builder.Append("BEGIN ");
            builder.Append("\tIF NOT EXISTS ( ");
            builder.Append("\t\t\tSELECT ");
            builder.Append("\t\t\t\t[AttachmentId] ");
            builder.Append("\t\t\tFROM ");
            builder.Append("\t\t\t\t[dbo].[Email] ");
            builder.Append("\t\t\tWHERE ");
            builder.Append("\t\t\t\t[AttachmentId] = @AttachmentId\t ");
            builder.Append("\t\t\t\tAND [DeleteFlag] <= 2 ");
            builder.Append("\t\t\t) ");
            builder.Append("\tBEGIN ");
            builder.Append("\t\tUPDATE ");
            builder.Append("\t\t\t[dbo].[Attachment] ");
            builder.Append("\t\tSET ");
            builder.Append("\t\t\t[DeleteFlag] = 1 ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[AttachmentId] = @AttachmentId ");
            builder.Append("\tEND\t ");
            builder.Append("END ");
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@EmailId", SqlDbType.Int);
            commandParameters[0].Value = emailId;
            commandParameters[1] = new SqlParameter("@DeleteType", SqlDbType.Int);
            commandParameters[1].Value = (int) deleteType;
            commandParameters[2] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[2].Value = userId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public bool DeleteRead(SqlConnection conn, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DECLARE @AttachmentIdTable TABLE(AttachmentId nvarchar(50)) ");
            builder.Append("UPDATE ");
            builder.Append("\t[dbo].[Email] ");
            builder.Append("SET ");
            builder.Append("\t[DeleteFlag] = [DeleteFlag] + 1 ");
            builder.Append("WHERE ");
            builder.Append("\t[ToId] = @UserId ");
            builder.Append("\tAND [ReadFlag] = 1 ");
            builder.Append("\tAND [DeleteFlag] IN (0,2) ");
            builder.Append("INSERT INTO @AttachmentIdTable ");
            builder.Append("SELECT ");
            builder.Append("\t[AttachmentId] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Email] ");
            builder.Append("WHERE ");
            builder.Append("\t[ToId] = @UserId ");
            builder.Append("\tAND ISNULL([AttachmentId],'') <> '' ");
            builder.Append("\tAND [DeleteFlag] > 2 ");
            builder.Append("DELETE FROM [dbo].[Email] ");
            builder.Append("WHERE ");
            builder.Append("\t[ToId] = @UserId ");
            builder.Append("\tAND [DeleteFlag] > 2 ");
            builder.Append("UPDATE ");
            builder.Append("\t[dbo].[Attachment] ");
            builder.Append("SET ");
            builder.Append("\t[DeleteFlag] = 1 ");
            builder.Append("WHERE ");
            builder.Append("\t[AttachmentId] IN ");
            builder.Append("\t\t\t( ");
            builder.Append("\t\t\tSELECT ");
            builder.Append("\t\t\t\t[AttachmentId] ");
            builder.Append("\t\t\tFROM ");
            builder.Append("\t\t\t\t@AttachmentIdTable ");
            builder.Append("\t\t\tWHERE ");
            builder.Append("\t\t\t\t[AttachmentId] NOT IN ( ");
            builder.Append("\t\t\t\t\t\t\tSELECT ");
            builder.Append("\t\t\t\t\t\t\t\t[AttachmentId] ");
            builder.Append("\t\t\t\t\t\t\tFROM ");
            builder.Append("\t\t\t\t\t\t\t\t[dbo].[Email] ");
            builder.Append("\t\t\t\t\t\t\tWHERE ");
            builder.Append("\t\t\t\t\t\t\t\t[DeleteFlag] <= 2 ");
            builder.Append("\t\t\t\t\t\t\t) ");
            builder.Append("\t\t\t) ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public void InboxBeforeNext(SqlConnection conn, int emailId, ref int beforeId, ref int nextId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DECLARE @BeforeId\tint ");
            builder.Append("DECLARE @NextId\tint ");
            builder.Append("SELECT TOP 1  ");
            builder.Append("\t@BeforeId = a.[EmailId] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Email] AS a, ");
            builder.Append("\t[dbo].[Email] AS b ");
            builder.Append("WHERE ");
            builder.Append("\tb.[EmailId] = @EmailId ");
            builder.Append("\tAND a.[BoxId] = b.[BoxId] ");
            builder.Append("\tAND a.[ToId] = b.[ToId] ");
            builder.Append("\tAND a.[DeleteFlag] IN (0,2) ");
            builder.Append("\tAND a.[EmailId] > b.[EmailId] ");
            builder.Append("ORDER BY ");
            builder.Append("\ta.[EmailId] ASC ");
            builder.Append("SELECT TOP 1  ");
            builder.Append("\t@NextId = a.[EmailId] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Email] AS a, ");
            builder.Append("\t[dbo].[Email] AS b ");
            builder.Append("WHERE ");
            builder.Append("\tb.[EmailId] = @EmailId ");
            builder.Append("\tAND a.[BoxId] = b.[BoxId] ");
            builder.Append("\tAND a.[ToId] = b.[ToId] ");
            builder.Append("\tAND a.[DeleteFlag] = b.[DeleteFlag] ");
            builder.Append("\tAND a.[EmailId] < b.[EmailId] ");
            builder.Append("ORDER BY ");
            builder.Append("\ta.[EmailId] DESC ");
            builder.Append("SELECT @BeforeId AS BeforeId,@NextId AS NextId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@EmailId", SqlDbType.Int) };
            commandParameters[0].Value = emailId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (set.Tables[0].Rows[0]["BeforeId"] != DBNull.Value)
            {
                beforeId = Convert.ToInt32(set.Tables[0].Rows[0]["BeforeId"].ToString());
            }
            if (set.Tables[0].Rows[0]["NextId"] != DBNull.Value)
            {
                nextId = Convert.ToInt32(set.Tables[0].Rows[0]["NextId"].ToString());
            }
        }

        public DataTable InboxSelectPaged(SqlConnection conn, int PageSize, int PageIndex, bool docount, int boxId, string userId, ref int totalIsNew)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t@TotalIsNew = COUNT([EmailId]) ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Email]\t ");
            builder.Append("WHERE ");
            builder.Append("\t[BoxId] = @BoxId ");
            builder.Append("\tAND [ToId]=@UserId ");
            builder.Append("\tAND [DeleteFlag] IN(0,2) ");
            builder.Append("\tAND [ReadFlag]=0 ");
            builder.Append("IF(@docount=1) ");
            builder.Append("\tSELECT ");
            builder.Append("\t\tCOUNT([EmailId]) ");
            builder.Append("\tFROM ");
            builder.Append("\t\t[dbo].[Email]\t\t ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[BoxId] = @BoxId ");
            builder.Append("\t\tAND [ToId]=@UserId ");
            builder.Append("\t\tAND [DeleteFlag] IN(0,2) ");
            builder.Append("ELSE ");
            builder.Append("\tBEGIN ");
            builder.Append("\t\tDECLARE @indextable table(ID int identity(1,1),PK int) ");
            builder.Append("\t\tDECLARE @PageLowerBound int ");
            builder.Append("\t\tDECLARE @PageUpperBound int ");
            builder.Append("\t\tSET @PageLowerBound=(@pageindex-1)*@pagesize ");
            builder.Append("\t\tSET @PageUpperBound=@PageLowerBound+@pagesize ");
            builder.Append("\t\tSET rowcount @PageUpperBound\t ");
            builder.Append("\t ");
            builder.Append("\t\tINSERT INTO ");
            builder.Append("\t\t\t@indextable(PK) ");
            builder.Append("\t\tSELECT ");
            builder.Append("\t\t\t[EmailId] ");
            builder.Append("\t\tFROM  ");
            builder.Append("\t\t\t[dbo].[Email] ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[BoxId] = @BoxId ");
            builder.Append("\t\t\tAND [ToId]=@UserId ");
            builder.Append("\t\t\tAND [DeleteFlag] IN(0,2) ");
            builder.Append("\t\tORDER BY ");
            builder.Append("\t\t\t[EmailId] DESC ");
            builder.Append("\t\t ");
            builder.Append("\t\tSELECT ");
            builder.Append("\t\t\t[Email].[EmailId], ");
            builder.Append("\t\t\t[Email].[BoxId], ");
            builder.Append("\t\t\t[Email].[Subject], ");
            builder.Append("\t\t\t[Email].[SendTime], ");
            builder.Append("\t\t\t[Email].[ReadFlag], ");
            builder.Append("\t\t\t[Email].[DeleteFlag], ");
            builder.Append("\t\t\t[Email].[Important], ");
            builder.Append("\t\t\t[Email].[TotalSize], ");
            builder.Append("\t\t\t[Users].[UserName], ");
            builder.Append("\t\t\t[Department].[DeptName], ");
            builder.Append("\t\t\t@pageindex AS pageindex  ");
            builder.Append("\t\tFROM ");
            builder.Append("\t\t\t[dbo].[Email], ");
            builder.Append("\t\t\t[dbo].[Users], ");
            builder.Append("\t\t\t[dbo].[Department], ");
            builder.Append("\t\t\t@indextable t ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[Email].[EmailId] = t.PK ");
            builder.Append("\t\t\tAND [Email].[BoxId] = @BoxId ");
            builder.Append("\t\t\tAND [Email].[FromId] = [Users].[UserId] ");
            builder.Append("\t\t\tAND [Users].[DeptId] = [Department].[DeptId] ");
            builder.Append("\t\t\tAND t.ID>@PageLowerBound AND t.ID<=@PageUpperBound ");
            builder.Append("\t\tORDER BY ");
            builder.Append("\t\t\t[Email].[EmailId] DESC ");
            builder.Append("\tEND ");
            SqlParameter[] commandParameters = new SqlParameter[6];
            commandParameters[0] = new SqlParameter("@pagesize", SqlDbType.Int);
            commandParameters[0].Value = PageSize;
            commandParameters[1] = new SqlParameter("@pageindex", SqlDbType.Int);
            commandParameters[1].Value = PageIndex;
            commandParameters[2] = new SqlParameter("@docount", SqlDbType.Bit);
            commandParameters[2].Value = docount;
            commandParameters[3] = new SqlParameter("@BoxId", SqlDbType.Int);
            commandParameters[3].Value = boxId;
            commandParameters[4] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[4].Value = userId;
            commandParameters[5] = new SqlParameter("@TotalIsNew", SqlDbType.Int);
            commandParameters[5].Direction = ParameterDirection.Output;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (commandParameters[5].Value != DBNull.Value)
            {
                totalIsNew = Convert.ToInt32(commandParameters[5].Value);
            }
            set.Tables[0].TableName = "Email";
            return set.Tables[0];
        }

        public int Insert(SqlConnection conn, EmailData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT INTO [dbo].[Email] ");
            builder.Append("( ");
            builder.Append("\t[BoxId], ");
            builder.Append("\t[FromId], ");
            builder.Append("\t[ToId], ");
            builder.Append("\t[ToId2], ");
            builder.Append("\t[CopyToId], ");
            builder.Append("\t[SecretToId], ");
            builder.Append("\t[Subject], ");
            builder.Append("\t[Content], ");
            builder.Append("\t[SendTime], ");
            builder.Append("\t[AttachmentId], ");
            builder.Append("\t[ReadFlag], ");
            builder.Append("\t[DeleteFlag], ");
            builder.Append("\t[TotalSize], ");
            builder.Append("\t[Important] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@BoxId, ");
            builder.Append("\t@FromId, ");
            builder.Append("\t@ToId, ");
            builder.Append("\t@ToId2, ");
            builder.Append("\t@CopyToId, ");
            builder.Append("\t@SecretToId, ");
            builder.Append("\t@Subject, ");
            builder.Append("\t@Content, ");
            builder.Append("\t@SendTime, ");
            builder.Append("\t@AttachmentId, ");
            builder.Append("\t@ReadFlag, ");
            builder.Append("\t@DeleteFlag, ");
            builder.Append("\t@TotalSize, ");
            builder.Append("\t@Important ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[14];
            commandParameters[0] = new SqlParameter("@BoxId", SqlDbType.Int);
            commandParameters[0].Value = data.BoxId;
            commandParameters[1] = new SqlParameter("@FromId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = data.FromId;
            commandParameters[2] = new SqlParameter("@ToId", SqlDbType.NVarChar, 20);
            commandParameters[2].Value = data.ToId;
            commandParameters[3] = new SqlParameter("@ToId2", SqlDbType.NText);
            commandParameters[3].Value = data.ToId2;
            commandParameters[4] = new SqlParameter("@CopyToId", SqlDbType.NText);
            if (data.CopyToIdIsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.CopyToId;
            }
            commandParameters[5] = new SqlParameter("@SecretToId", SqlDbType.NText);
            if (data.SecretToIdIsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.SecretToId;
            }
            commandParameters[6] = new SqlParameter("@Subject", SqlDbType.NVarChar, 200);
            commandParameters[6].Value = data.Subject;
            commandParameters[7] = new SqlParameter("@Content", SqlDbType.NText);
            if (data.ContentIsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.Content;
            }
            commandParameters[8] = new SqlParameter("@SendTime", SqlDbType.DateTime);
            commandParameters[8].Value = data.SendTime;
            commandParameters[9] = new SqlParameter("@AttachmentId", SqlDbType.NVarChar, 50);
            if (data.AttachmentIdIsNull)
            {
                commandParameters[9].Value = DBNull.Value;
            }
            else
            {
                commandParameters[9].Value = data.AttachmentId;
            }
            commandParameters[10] = new SqlParameter("@ReadFlag", SqlDbType.Bit);
            commandParameters[10].Value = data.ReadFlag;
            commandParameters[11] = new SqlParameter("@DeleteFlag", SqlDbType.Int);
            commandParameters[11].Value = data.DeleteFlag;
            commandParameters[12] = new SqlParameter("@TotalSize", SqlDbType.Int);
            commandParameters[12].Value = data.TotalSize;
            commandParameters[13] = new SqlParameter("@Important", SqlDbType.Int);
            commandParameters[13].Value = data.Important;
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public void OutboxBeforeNext(SqlConnection conn, int emailId, ref int beforeId, ref int nextId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DECLARE @BeforeId int ");
            builder.Append("DECLARE @NextId\tint ");
            builder.Append("SELECT TOP 1  ");
            builder.Append("\t@BeforeId = a.[EmailId] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Email] AS a, ");
            builder.Append("\t[dbo].[Email] AS b ");
            builder.Append("WHERE ");
            builder.Append("\tb.[EmailId] = @EmailId ");
            builder.Append("\tAND a.[BoxId] = b.[BoxId] ");
            builder.Append("\tAND a.[FromId] = b.[FromId] ");
            builder.Append("\tAND a.[DeleteFlag] = b.[DeleteFlag] ");
            builder.Append("\tAND a.[EmailId] > b.[EmailId] ");
            builder.Append("ORDER BY ");
            builder.Append("\ta.[EmailId] ASC ");
            builder.Append("SELECT TOP 1  ");
            builder.Append("\t@NextId = a.[EmailId] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Email] AS a, ");
            builder.Append("\t[dbo].[Email] AS b ");
            builder.Append("WHERE ");
            builder.Append("\tb.[EmailId] = @EmailId ");
            builder.Append("\tAND a.[BoxId] = b.[BoxId] ");
            builder.Append("\tAND a.[FromId] = b.[FromId] ");
            builder.Append("\tAND a.[DeleteFlag] IN(0,1) ");
            builder.Append("\tAND a.[EmailId] < b.[EmailId] ");
            builder.Append("ORDER BY ");
            builder.Append("\ta.[EmailId] DESC ");
            builder.Append("SELECT @BeforeId AS BeforeId,@NextId AS NextId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@EmailId", SqlDbType.Int) };
            commandParameters[0].Value = emailId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (set.Tables[0].Rows[0]["BeforeId"] != DBNull.Value)
            {
                beforeId = Convert.ToInt32(set.Tables[0].Rows[0]["BeforeId"].ToString());
            }
            if (set.Tables[0].Rows[0]["NextId"] != DBNull.Value)
            {
                nextId = Convert.ToInt32(set.Tables[0].Rows[0]["NextId"].ToString());
            }
        }

        public DataTable OutboxSelectPaged(SqlConnection conn, string userId, int PageSize, int PageIndex, bool docount)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF(@docount=1) ");
            builder.Append("\tSELECT ");
            builder.Append("\t\tCOUNT([EmailId]) ");
            builder.Append("\tFROM ");
            builder.Append("\t\t[dbo].[Email]\t\t ");
            builder.Append("\tWHERE ");
            builder.Append("\t\t[FromId]=@UserId ");
            builder.Append("\t\tAND [DeleteFlag] IN(0,1) ");
            builder.Append("ELSE ");
            builder.Append("\tBEGIN ");
            builder.Append("\t\tDECLARE @indextable table(ID int identity(1,1),PK int) ");
            builder.Append("\t\tDECLARE @PageLowerBound int ");
            builder.Append("\t\tDECLARE @PageUpperBound int ");
            builder.Append("\t\tSET @PageLowerBound=(@pageindex-1)*@pagesize ");
            builder.Append("\t\tSET @PageUpperBound=@PageLowerBound+@pagesize ");
            builder.Append("\t\tSET rowcount @PageUpperBound ");
            builder.Append("\t\tINSERT INTO ");
            builder.Append("\t\t\t@indextable(PK) ");
            builder.Append("\t\tSELECT ");
            builder.Append("\t\t\t[EmailId] ");
            builder.Append("\t\tFROM  ");
            builder.Append("\t\t\t[dbo].[Email] ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[FromId] = @UserId ");
            builder.Append("\t\t\tAND [DeleteFlag] IN(0,1) ");
            builder.Append("\t\tORDER BY ");
            builder.Append("\t\t\t[EmailId] DESC ");
            builder.Append("\t ");
            builder.Append("\t\tSELECT ");
            builder.Append("\t\t\t[Email].[EmailId], ");
            builder.Append("\t\t\t[Email].[Subject], ");
            builder.Append("\t\t\t[Email].[SendTime], ");
            builder.Append("\t\t\t[Email].[ReadFlag], ");
            builder.Append("\t\t\t[Email].[DeleteFlag], ");
            builder.Append("\t\t\t[Email].[Important], ");
            builder.Append("\t\t\t[Email].[TotalSize], ");
            builder.Append("\t\t\t[Users].[UserName], ");
            builder.Append("\t\t\t[Department].[DeptName], ");
            builder.Append("\t\t\t@pageindex AS pageindex  ");
            builder.Append("\t\tFROM ");
            builder.Append("\t\t\t[dbo].[Email], ");
            builder.Append("\t\t\t[dbo].[Users], ");
            builder.Append("\t\t\t[dbo].[Department], ");
            builder.Append("\t\t\t@indextable t ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[Email].[EmailId] = t.PK ");
            builder.Append("\t\t\tAND [Email].[ToId] = [Users].[UserId] ");
            builder.Append("\t\t\tAND [Users].[DeptId] = [Department].[DeptId] ");
            builder.Append("\t\t\tAND t.ID>@PageLowerBound AND t.ID<=@PageUpperBound ");
            builder.Append("\t\tORDER BY ");
            builder.Append("\t\t\t[Email].[EmailId] DESC ");
            builder.Append("END ");
            SqlParameter[] commandParameters = new SqlParameter[4];
            commandParameters[0] = new SqlParameter("@pagesize", SqlDbType.Int);
            commandParameters[0].Value = PageSize;
            commandParameters[1] = new SqlParameter("@pageindex", SqlDbType.Int);
            commandParameters[1].Value = PageIndex;
            commandParameters[2] = new SqlParameter("@docount", SqlDbType.Bit);
            commandParameters[2].Value = docount;
            commandParameters[3] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[3].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Email";
            return set.Tables[0];
        }

        public DataTable SelectByEmailId(SqlConnection conn, int emailId, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE ");
            builder.Append("\t[dbo].[Email] ");
            builder.Append("SET ");
            builder.Append("\t[ReadFlag] = 1 ");
            builder.Append("WHERE ");
            builder.Append("\t[EmailId] = @EmailId ");
            builder.Append("\t AND [ToId] = @UserId ");
            builder.Append("\t AND [ReadFlag] = 0 ");
            builder.Append("SELECT\t ");
            builder.Append("\t[Email].*, ");
            builder.Append("\t[Users].[UserName], ");
            builder.Append("\tdbo.GetUserNames(CAST(ToId2 AS nvarchar(4000))) AS ToName, ");
            builder.Append("\tdbo.GetUserNames(CAST([CopyToId] AS nvarchar(4000))) AS CopyToName ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Email], ");
            builder.Append("\t[dbo].[Users] ");
            builder.Append("WHERE ");
            builder.Append("\t[Email].[EmailId] = @EmailId ");
            builder.Append("\tAND [Email].[FromId] = [Users].[UserId]\t ");
            builder.Append("\tAND ( ");
            builder.Append("\t\t([Email].[FromId] = @UserId) ");
            builder.Append("\t\tOR ([Email].[ToId] = @UserId) ");
            builder.Append("\t        ) ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@EmailId", SqlDbType.Int);
            commandParameters[0].Value = emailId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Email";
            return set.Tables[0];
        }

        public DataTable SelectMyTable(SqlConnection conn, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT TOP " + new ConfigManager().EmailNum.ToString());
            builder.Append("\t[Email].[EmailId], ");
            builder.Append("\t[Email].[BoxId], ");
            builder.Append("\t[Email].[Subject], ");
            builder.Append("\t[Email].[SendTime], ");
            builder.Append("\t[Email].[ReadFlag], ");
            builder.Append("\t[Email].[Important],\t ");
            builder.Append("\t[Users].[UserName], ");
            builder.Append("\t[Department].[DeptName] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Email], ");
            builder.Append("\t[dbo].[Users], ");
            builder.Append("\t[dbo].[Department] ");
            builder.Append("WHERE ");
            builder.Append("\t[Email].[BoxId] = 0 ");
            builder.Append("\tAND ([Email].[ToId] = @UserId)  ");
            builder.Append("\tAND ([Email].[FromId] = [Users].[UserId])  ");
            builder.Append("\tAND ([Users].[DeptId] = [Department].[DeptId])  ");
            builder.Append("\tAND ([DeleteFlag] IN(0,2)) ");
            builder.Append("ORDER BY ");
            builder.Append("\t[Email].[EmailId] DESC ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Email";
            return set.Tables[0];
        }

        public void Statistic(SqlConnection conn, string userId, ref int inboxNums, ref int inboxNewNums, ref int outboxNums)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t@InboxNums = COUNT([EmailId]) ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Email] ");
            builder.Append("WHERE ");
            builder.Append("\t[BoxId] = 0 ");
            builder.Append("\tAND [ToId] = @UserId ");
            builder.Append("\tAND [DeleteFlag] IN (0,2) ");
            builder.Append("SELECT ");
            builder.Append("\t@InboxNewNums = COUNT([EmailId]) ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Email] ");
            builder.Append("WHERE ");
            builder.Append("\t[BoxId] = 0 ");
            builder.Append("\tAND [ToId] = @UserId ");
            builder.Append("\tAND [DeleteFlag] IN (0,2) ");
            builder.Append("\tAND [ReadFlag] = 0 ");
            builder.Append("SELECT ");
            builder.Append("\t@OutboxNums = COUNT([EmailId]) ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Email] ");
            builder.Append("WHERE ");
            builder.Append("\t[FromId] = @UserId ");
            builder.Append("\tAND [DeleteFlag] IN (0,1) ");
            SqlParameter[] commandParameters = new SqlParameter[4];
            commandParameters[0] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[0].Value = userId;
            commandParameters[1] = new SqlParameter("@InboxNums", SqlDbType.Int);
            commandParameters[1].Direction = ParameterDirection.Output;
            commandParameters[2] = new SqlParameter("@InboxNewNums", SqlDbType.Int);
            commandParameters[2].Direction = ParameterDirection.Output;
            commandParameters[3] = new SqlParameter("@OutboxNums", SqlDbType.Int);
            commandParameters[3].Direction = ParameterDirection.Output;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (commandParameters[1].Value != DBNull.Value)
            {
                inboxNums = Convert.ToInt32(commandParameters[1].Value);
            }
            if (commandParameters[2].Value != DBNull.Value)
            {
                inboxNewNums = Convert.ToInt32(commandParameters[2].Value);
            }
            if (commandParameters[3].Value != DBNull.Value)
            {
                outboxNums = Convert.ToInt32(commandParameters[3].Value);
            }
        }

        public enum DeleteType
        {
            Inbox,
            Outbox
        }
    }
}

